package com.usa.tool.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.usa.tool.entity.UserActionRecord;
import com.usa.tool.model.UserActionPhotoBo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author YueGod
 * @since 2023/8/29
 */
public interface UserActionRecordMapper extends BaseMapper<UserActionRecord> {

    @Select("select s2.accountId as 'accountId',\n" +
            "       t2.img_url   as 'url'\n" +
            "from (select s1.accountId  as 'accountId',\n" +
            "             count(s1.url) as 'total'\n" +
            "      from (select t1.account_id as 'accountId',\n" +
            "                   t2.img_url    as 'url'\n" +
            "            from tb_user_action_record t1\n" +
            "                     left join tb_user_photo t2 on t1.account_id = t2.account_id\n" +
            "            where t1.channel in ('bofire_App Store', 'surprisme_App Store')\n" +
            "              and t1.action_name = 'LOCATION'\n" +
            "              and t1.type = 'ON'\n" +
            "              and t1.create_time between '2023-08-13 00:00:00' and '2023-08-13 05:00:00'\n" +
            "              and t2.img_url not in ('bofire_img_213')\n" +
            "              and t2.type = 1\n" +
            "              and t2.use_type = 1\n" +
            "            union\n" +
            "            select t1.account_id as 'accountId',\n" +
            "                   t2.img_url    as 'url'\n" +
            "            from tb_user_action_record t1\n" +
            "                     left join tb_user_photo t2 on t1.account_id = t2.account_id\n" +
            "            where t1.channel in ('bofire_App Store', 'surprisme_App Store')\n" +
            "              and t1.action_name = 'REG_PAGE'\n" +
            "              and t1.type = 'sex'\n" +
            "              and t1.create_time between '2023-08-13 00:00:00' and '2023-08-13 05:00:00'\n" +
            "              and t2.img_url not in ('bofire_img_213')\n" +
            "              and t2.type = 1\n" +
            "              and t2.use_type = 1) s1\n" +
            "      group by s1.accountId) s2\n" +
            "         left join tb_user_photo t2 on s2.accountId = t2.account_id\n" +
            "where s2.total = 4\n" +
            "  and t2.img_url not in ('bofire_img_213')\n" +
            "  and t2.type = 1\n")
    List<UserActionPhotoBo> findUser();

}
